package cn.lvji.sus.db;

import cn.lvji.sus.model.Links;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import static org.slf4j.LoggerFactory.getLogger;

/**
 * jdbc封装
 *
 * @author zhouzx
 */
public class JDBCUtils {
    private static final Logger logger = getLogger(JDBCUtils.class);
    private static final String ADD_SQL = "insert into t_links(long_url,short_url) values(?,?)";
    private static final String QUERY_SQL = "select long_url from t_links where short_url = ?";

    /**
     * insert update delete SQL语句的执行的统一方法
     *
     * @param sql    SQL语句
     * @param params 参数数组，若没有参数则为null
     * @return 影响的行数
     */
    public static int executeUpdate(String sql, Object[] params) {
        int affectedLine = 0;
        try (Connection conn = ConnectionFactory.getConnection();
             PreparedStatement pst = conn.prepareStatement(sql)) {
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pst.setObject(i + 1, params[i]);
                }
            }
            affectedLine = pst.executeUpdate();
        } catch (SQLException e) {
            logger.error("execute sql error!", e);
        }
        return affectedLine;
    }

    /**
     * SQL 查询将查询结果：一行一列
     *
     * @param sql    SQL语句
     * @param params 参数数组，若没有参数则为null
     * @return 结果集
     */
    public static Object executeQuerySingle(String sql, Object[] params) {
        Object object = null;
        try (Connection connection = ConnectionFactory.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(sql
             )) {
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    preparedStatement.setObject(i + 1, params[i]);
                }
            }
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    object = resultSet.getObject(1);
                }
            }
        } catch (Exception e) {
            logger.error("query from db error!", e);
        }
        return object;
    }

    public static void addLink(Links links) {
        executeUpdate(ADD_SQL, new Object[]{links.getLongUrl(), links.getShortUrl()});
    }

    public static String getLongUrl(String shortUrl) {
        return (String) executeQuerySingle(QUERY_SQL, new Object[]{shortUrl});
    }

}
